﻿using AduSkin.Controls.Metro;
using HsServerHa.Common.Auth;
using HsServerHa.Entity.Models;
using HsServerHa.Utility;
using HsServerHa.Utility.Computer;
using HsServerHa.Utility.DataHelper;
using HsServerHa.Utility.NetWork;
using HsServerHa.Utility.SQLite;
using Quartz;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Documents;

namespace HsServerHa.Jobs
{
    /// <summary>
    /// 该属性告诉Quartz不要同时执行给定Job定义
    /// </summary>
    [DisallowConcurrentExecution]
    public class CheckSQLTaskJob : IJob
    {
        public async Task Execute(IJobExecutionContext context)
        {
            #region 授权验证
            if (!AuthorizationVerification.IsVerification())
            {
                return ;
            } 
            #endregion
            try
            {
                if (App.isMaster != null)
                {
                    if (App.isMaster.Value)
                    {//主机检查数据库是否是主
                     //查询数据
                     //var sql = "SELECT * FROM SQLManage where IsEnable='是'";
                     //var dt = DbHelperSQLite.Query(sql)?.Tables?[0];
                     //List<SQLManageEntity> list = DataHelpers.ConvertToModel<SQLManageEntity>(dt);

                        //var list = await SQLiteHelper.TableAsync<SQLManageEntity>().Where(x => x.IsEnable == "是").ToListAsync().ConfigureAwait(false);
                        var list = await SQLiteHelper.TableAsync<SQLManageEntity>().Where(x => x.IsEnable == "是").ToListAsync().ConfigureAwait(false);
                        if (list==null)
                        {
                            return;
                        }
                        foreach (var item in list)
                        {
                            var DBConnectionStr = ConfigurationManager.AppSettings["DBConnectionString"].ToString();
                            DBConnectionStr = DBConnectionStr.Replace("#IPAddress#", App.CurrIP + "," + item.Port);
                            DBConnectionStr = DBConnectionStr.Replace("#DBName#", "master");
                            DBConnectionStr = DBConnectionStr.Replace("#UserId#", item.UserId);
                            DBConnectionStr = DBConnectionStr.Replace("#UserPwd#", item.UserPwd);
                            DbHelperSQL db = new DbHelperSQL(DBConnectionStr);
                            #region 查询语句
                            var firstSQL = db.QueryFirstOrDefault<SQLMasterOrSlaveEntity>($@"SELECT
                                    DB_NAME(database_id) AS DatabaseName,
                                    dm.mirroring_role AS MirroringRole,
                                    (CASE dm.mirroring_role
                                       WHEN 1 THEN '主体'
                                       WHEN 2 THEN '镜像'
                                       END) AS MirroringRoleDesc,
                                    dm.mirroring_partner_name AS MirroringPartnerName,
                                    (CASE WHEN dm.mirroring_witness_name IS NULL
                                       THEN '--'
                                       ELSE dm.mirroring_witness_name
                                    END)AS MirroringWitnessName,
                                    dm.mirroring_state AS MirroringState,
                                    (CASE dm.mirroring_state
                                       WHEN 0 THEN '已挂起'
                                       WHEN 1 THEN '与其他伙伴断开'
                                       WHEN 2 THEN '正在同步'
                                       WHEN 3 THEN '挂起故障转移'
                                       WHEN 4 THEN '已同步'
                                       WHEN 5 THEN '伙伴未同步'
                                       WHEN 6 THEN '伙伴已同步'
                                       WHEN NULL THEN '无镜像'
                                    END) AS MirroringStateDesc,
                                    dm.mirroring_safety_level AS MirroringSafetyLevel,
                                    (CASE dm.mirroring_safety_level
                                       WHEN 0 THEN '未知'
                                       WHEN 1 THEN '异步'
                                       WHEN 2 THEN '同步'
                                       WHEN NULL THEN '无镜像'
                                    END) AS MirroringSafetyLevelDesc,
                                    dm.mirroring_witness_state AS MirroringWitnessState,
                                    (CASE dm.mirroring_witness_state
                                       WHEN 0 THEN '见证未知'
                                       WHEN 1 THEN '见证连接'
                                       WHEN 2 THEN '见证断开'
                                       WHEN NULL THEN '无见证'
                                    END) AS MirroringWitnessStateDesc
                                    FROM sys.database_mirroring dm
                                    WHERE dm.mirroring_guid IS NOT NULL and  DB_NAME(database_id)='{item.DBName}'");
                            #endregion
                            if (firstSQL != null)
                            {
                                if (firstSQL?.MirroringRoleDesc != "主体")
                                {
                                    Task.Run(() => {
                                        try
                                        {
                                            if (App.IsPingRefer && App.XtIsOpen)
                                            {

                                                //var sqlBind = "select * from MasterSlaveBind";
                                                //var dtBind = DbHelperSQLite.Query(sqlBind)?.Tables?[0];
                                                //List<MasterSlaveBindEntity> listBind = DataHelpers.ConvertToModel<MasterSlaveBindEntity>(dtBind);

                                                List<MasterSlaveBindEntity> listBind= SQLiteHelper.Table<MasterSlaveBindEntity>().ToList();

                                                var bindFirst = listBind.FirstOrDefault();

                                                DBConnectionStr = ConfigurationManager.AppSettings["DBConnectionString"].ToString();
                                                DBConnectionStr = DBConnectionStr.Replace("#IPAddress#", bindFirst.SlaveIP + "," + item.Port);
                                                DBConnectionStr = DBConnectionStr.Replace("#DBName#", "master");
                                                DBConnectionStr = DBConnectionStr.Replace("#UserId#", item.UserId);
                                                DBConnectionStr = DBConnectionStr.Replace("#UserPwd#", item.UserPwd);
                                                db = new DbHelperSQL(DBConnectionStr);
                                                db.ExecuteSql($"USE master;  ALTER DATABASE [{item.DBName}] SET PARTNER FAILOVER");
                                                db.ExecuteSql($"USE master; ALTER DATABASE [{item.DBName}] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;");

                                            }
                                            else
                                            {
                                                db.ExecuteSql($"USE master;  ALTER DATABASE [{item.DBName}] SET PARTNER FAILOVER");
                                                db.ExecuteSql($"USE master; ALTER DATABASE [{item.DBName}] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;");

                                            }

                                        }
                                        catch (Exception ex)
                                        {
                                            LoggerHelper.Error(ex.Message+"详情："+ex.ToString());
                                        }
                                    });

                                }
                                if (firstSQL?.MirroringStateDesc == "已挂起")
                                {
                                    Task.Run(() => {
                                        try
                                        {
                                            db.ExecuteSql($"USE master; ALTER DATABASE [{item.DBName}] SET PARTNER RESUME; ");
                                        }
                                        catch (Exception ex)
                                        {
                                            LoggerHelper.Error(ex.Message+",详情2:"+ex.ToString());
                                        }
                                    });

                                }
                            }
                        }
                    }
                    else
                    {
                        //var dtXt = DbHelperSQLite.Query("select * from  HeartManage where IsLocal='true'")?.Tables?[0];
                        //var listXt = DataHelpers.ConvertToModel<HeartEntity>(dtXt);

                        var listXt= await SQLiteHelper.TableAsync<HeartEntity>().Where(x=>x.IsLocal=="true").ToListAsync().ConfigureAwait(false);
                        if (listXt == null)
                        {
                            return;
                        }
                        if (NetWorkHelper.IsPingIP(listXt[0].HeartIP) && NetWorkHelper.IsPingIP(string.IsNullOrEmpty(listXt[0].MasterHeartIPBind) ? listXt[0].HeartIP : listXt[0].MasterHeartIPBind))
                        {
                            //查询数据
                            //var sql = "SELECT * FROM SQLManage where IsEnable='是'";
                            //var dt = DbHelperSQLite.Query(sql)?.Tables?[0];
                            //List<SQLManageEntity> list = DataHelpers.ConvertToModel<SQLManageEntity>(dt);
                            var list = await SQLiteHelper.TableAsync<SQLManageEntity>().Where(x => x.IsEnable == "是").ToListAsync().ConfigureAwait(false);

                            foreach (var item in list)
                            {

                                var DBConnectionStr = ConfigurationManager.AppSettings["DBConnectionString"].ToString();
                                DBConnectionStr = DBConnectionStr.Replace("#IPAddress#", ".");
                                DBConnectionStr = DBConnectionStr.Replace("#DBName#", "master");
                                DBConnectionStr = DBConnectionStr.Replace("#UserId#", item.UserId);
                                DBConnectionStr = DBConnectionStr.Replace("#UserPwd#", item.UserPwd);
                                DbHelperSQL db = new DbHelperSQL(DBConnectionStr);
                                #region 查询语句
                                var firstSQL = db.QueryFirstOrDefault<SQLMasterOrSlaveEntity>($@"SELECT
                                    DB_NAME(database_id) AS DatabaseName,
                                    dm.mirroring_role AS MirroringRole,
                                    (CASE dm.mirroring_role
                                       WHEN 1 THEN '主体'
                                       WHEN 2 THEN '镜像'
                                       END) AS MirroringRoleDesc,
                                    dm.mirroring_partner_name AS MirroringPartnerName,
                                    (CASE WHEN dm.mirroring_witness_name IS NULL
                                       THEN '--'
                                       ELSE dm.mirroring_witness_name
                                    END)AS MirroringWitnessName,
                                    dm.mirroring_state AS MirroringState,
                                    (CASE dm.mirroring_state
                                       WHEN 0 THEN '已挂起'
                                       WHEN 1 THEN '与其他伙伴断开'
                                       WHEN 2 THEN '正在同步'
                                       WHEN 3 THEN '挂起故障转移'
                                       WHEN 4 THEN '已同步'
                                       WHEN 5 THEN '伙伴未同步'
                                       WHEN 6 THEN '伙伴已同步'
                                       WHEN NULL THEN '无镜像'
                                    END) AS MirroringStateDesc,
                                    dm.mirroring_safety_level AS MirroringSafetyLevel,
                                    (CASE dm.mirroring_safety_level
                                       WHEN 0 THEN '未知'
                                       WHEN 1 THEN '异步'
                                       WHEN 2 THEN '同步'
                                       WHEN NULL THEN '无镜像'
                                    END) AS MirroringSafetyLevelDesc,
                                    dm.mirroring_witness_state AS MirroringWitnessState,
                                    (CASE dm.mirroring_witness_state
                                       WHEN 0 THEN '见证未知'
                                       WHEN 1 THEN '见证连接'
                                       WHEN 2 THEN '见证断开'
                                       WHEN NULL THEN '无见证'
                                    END) AS MirroringWitnessStateDesc
                                    FROM sys.database_mirroring dm
                                    WHERE dm.mirroring_guid IS NOT NULL and  DB_NAME(database_id)='{item.DBName}'");
                                #endregion
                                if (firstSQL != null)
                                {
                                    if (firstSQL?.MirroringRoleDesc == "主体")
                                    {
                                        Task.Run(() => {
                                            try
                                            {
                                                db.ExecuteSql($"USE master;  ALTER DATABASE [{item.DBName}] SET PARTNER FAILOVER");
                                                db.ExecuteSql($"USE master; ALTER DATABASE [{item.DBName}] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;");

                                            }
                                            catch (Exception ex)
                                            {
                                                LoggerHelper.Error(ex.Message+":"+ex.ToString());
                                            }
                                        });

                                    }
                                    //if (firstSQL.MirroringStateDesc == "已挂起")
                                    //{
                                    //    Task.Run(() => {
                                    //        try
                                    //        {
                                    //            db.ExecuteSql($"USE master; ALTER DATABASE {item.DBName} SET PARTNER RESUME; ");
                                    //        }
                                    //        catch (Exception ex)
                                    //        {
                                    //            LoggerHelper.Error(ex.Message);
                                    //        }
                                    //    });

                                    //}
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LoggerHelper.Error(ex.Message+ex.ToString());
                LoggerHelper.Error(ex.StackTrace);
            }
         
        }
    }
}
